package com.hospital_queuing_call_system.dao.impl;

import com.hospital_queuing_call_system.bean.*;
import com.hospital_queuing_call_system.dao.UserDao;
import com.hospital_queuing_call_system.utils.JDBCUtils;
import jdk.nashorn.internal.scripts.JD;
import org.junit.Before;
import org.junit.Test;

import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.sql.Date;
import java.util.*;

/**
 * Created by CJBLY on 2023-05-10 21:02
 */
public class UserDaoImpl implements UserDao {


    public UserDaoImpl() {
    }

    /**
    *功能描述：用户登录
    *@param userName 用户账号
    *@param userPassword 用户密码
    *@return
    *           false 登录失败
    *           true  登陆成功
    */
    @Override
    public boolean loginIn(String userName, String userPassword) {
        //登录状态
        boolean logonStatus = false;
        //操作数据库
        String sql="select * from user where username=? and password=?";

        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement = null;
        //改变登录方法,使用PreparedStatement实现,可解决sql注入问题
        try {
            //connection.setAutoCommit(false);//事务自动提交关闭
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,userName);
            preparedStatement.setString(2,userPassword);

            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                logonStatus=true;
            }
            else logonStatus=false;
            //关闭数据库
            JDBCUtils.close(connection,preparedStatement,resultSet);
            return logonStatus;
        } catch (
                SQLException e) {
            throw new RuntimeException(e);
        }

    }

    /**
    *功能描述：用户注册
    *@param userName 用户账号
    *@param userPassword 用户密码
    *@return
    *           false 注册失败
    *           true  注册成功
    */
    @Override
    public boolean register(String userName, String userPassword){
        //用户注册状态
        boolean registrationStatus = false;
        //查询是否存在相同账号
        String selectSql = "select * from user where username=?";
        //用户注册状态正确时执行
        String insertSql = "insert into user (username,password) values(?,?)";
        //连接数据库
        Connection connection = JDBCUtils.getConnection();

        //用于执行预编译sql语句
        PreparedStatement preparedStatement = null;
        try{
            preparedStatement = connection.prepareStatement(selectSql);
            preparedStatement.setString(1,userName);

            //用于封装查询出来的数据
            ResultSet resultSet = preparedStatement.executeQuery();//执行预编译的查询sql语句（DQL）
            if(!resultSet.next()){
                registrationStatus=true;
            }
            if(registrationStatus){
                connection.setAutoCommit(false);//事务自动提交关闭
                preparedStatement = connection.prepareStatement(insertSql);
                preparedStatement.setString(1,userName);
                preparedStatement.setString(2,userPassword);
                int count = preparedStatement.executeUpdate();//执行预编译的更新sql语句（DDL，DML）
                if(count==1){
                    connection.commit();
                    System.out.println("注册成功");
                } else{
                    System.out.println("注册失败");
                    //事务回滚
                    connection.rollback();

                }
            }

            //关闭数据库
            JDBCUtils.close(connection,preparedStatement,resultSet);

        }catch (Exception e){
            e.printStackTrace();
            System.out.println(e.getMessage());
        }
        return registrationStatus;
    }

    /**
    *功能描述：查询科室
    *@return
    *           Map 一个或多个科室的信息
    */
    @Override
    public Map<String,List<Department>> selectDepartment(){

        String selectSql = "select * from department";
        //连接数据库
        Connection connection = JDBCUtils.getConnection();
        //用于执行预编译sql语句
        PreparedStatement preparedStatement = null;
        //用于封装查询出来的数据
        ResultSet resultSet = null;
        //用于封装同一个科室的科
        List<Department> departmentList = new ArrayList<>();
        //用于封装多个科室的信息
        Map<String, List<Department> > departmentMap = new HashMap<>();
        String department="";//科室
        String section;//科
        String description;//科的描述
        String preDepartment="";//前一个科室

        try {

            preparedStatement = connection.prepareStatement(selectSql);
            resultSet = preparedStatement.executeQuery();

            while(resultSet.next()){

                department = resultSet.getString(2);
                section = resultSet.getString(3);
                description = resultSet.getString(4);

                if(departmentMap.containsKey(department)){
                    departmentMap.get(department).add(new Department(department,section,description));
                }else {
                    departmentList = new ArrayList<>();
                    departmentList.add(new Department(department,section,description));
                    departmentMap.put(department,departmentList);
                }

//                if(department.equals(preDepartment)){
//                    departmentList.add(new Department(department,section,description));
//                } else{
//                    if(!preDepartment.equals(""))
//                        departmentMap.put(preDepartment,departmentList);
//                    preDepartment = department;
//                    departmentList = new ArrayList<>();
//                    departmentList.add(new Department(department,section,description));
//                }

            }
//            departmentMap.put(department,departmentList);

        }catch (Exception e){
            System.out.println(e.getMessage());
        }finally {
            JDBCUtils.close(connection,preparedStatement,resultSet);
        }
        return departmentMap;
    }

    /**
     *功能描述：用户查看所有缴费信息
     *@param userName 用户的账号
     *@return
     *           List 该用户下所有就诊人的缴费信息
     */
    @Override
    public List<PaymentInformation> selectPaymentInformationByUserName(String userName) {

        String selectSql = "select * from payment_information where user_name = ?";

        String orderNumber;//缴费订单号（主键）
        String paymentAmount;//缴费金额
        String patientVisitorName;//就诊人姓名
        String patientVisitorSex;//就诊人性别
        int    patientVisitorAge;//就诊人年龄
        String registeredLevel;//就诊人挂号级别
        String registeredDepartment;//就诊人挂号科室
        String consultDoctorName;//看诊医生姓名

        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        List<PaymentInformation> paymentInformationList = new ArrayList<>();

        try{
            preparedStatement = connection.prepareStatement(selectSql);
            preparedStatement.setString(1,userName);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                orderNumber = resultSet.getString(3);//缴费订单号（主键）
                paymentAmount = resultSet.getString(4);//缴费金额
                patientVisitorName = resultSet.getString(5);//就诊人姓名
                patientVisitorSex = resultSet.getString(6);//就诊人性别
                patientVisitorAge = Integer.parseInt(resultSet.getString(7));//就诊人年龄
                registeredLevel = resultSet.getString(8);//就诊人挂号级别
                registeredDepartment = resultSet.getString(9);//就诊人挂号科室
                consultDoctorName = resultSet.getString(10);//看诊医生姓名

                paymentInformationList.add(new PaymentInformation(userName,orderNumber,paymentAmount,
                        patientVisitorName,patientVisitorSex,patientVisitorAge,registeredLevel,
                        registeredDepartment,consultDoctorName));
                System.out.println("1");
            }


        }catch (Exception e){
            System.out.println(e.getMessage());
        }finally {
            JDBCUtils.close(connection,preparedStatement,resultSet);
        }

        return paymentInformationList;
    }

    /**
     *功能描述：插入一条缴费信息
     *@param paymentInformation 缴费信息
     *@return
     *           false 插入失败
     *           true 插入成功
     */
    @Override
    public boolean insertPayInformation(PaymentInformation paymentInformation) {

        boolean insertStatus=false;

        String insertSql = "insert into payment_information(user_name,order_number,payment_amount,patient_visitor_name," +
                "patient_visitor_sex,patient_visitor_age,registered_level,registered_department,consult_doctor_name)" +
                "values(?,?,?,?,?,?,?,?,?)";
        String orderNumber = paymentInformation.getOrderNumber();//缴费订单号（主键）
        String paymentAmount = paymentInformation.getPaymentAmount();//缴费金额
        String patientVisitorName = paymentInformation.getPatientVisitorName();//就诊人姓名
        String patientVisitorSex = paymentInformation.getPatientVisitorSex();//就诊人性别
        int    patientVisitorAge = paymentInformation.getPatientVisitorAge();//就诊人年龄
        String registeredLevel = paymentInformation.getRegisteredLevel();//就诊人挂号级别
        String registeredDepartment = paymentInformation.getRegisteredDepartment();//就诊人挂号科室
        String consultDoctorName = paymentInformation.getConsultDoctorName();//看诊医生姓名
        String userName = paymentInformation.getUserName();//用户账号

        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement = null;

        try {
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(insertSql);
            preparedStatement.setString(1,orderNumber);
            preparedStatement.setString(2,paymentAmount);
            preparedStatement.setString(3,patientVisitorName);
            preparedStatement.setString(4,patientVisitorSex);
            preparedStatement.setInt(5, patientVisitorAge);//转换成String
            preparedStatement.setString(6,registeredLevel);
            preparedStatement.setString(7,registeredDepartment);
            preparedStatement.setString(8,consultDoctorName);
            preparedStatement.setString(9,userName);

            int count = preparedStatement.executeUpdate();
            if(count==1){
                connection.commit();
                insertStatus = true;
                System.out.println("插入缴费信息成功");
            }else {
                connection.rollback();
                System.out.println("插入缴费信息失败");
            }

        }catch (Exception e){

        }finally {
            JDBCUtils.close(connection,preparedStatement,null);
        }

        return insertStatus;
    }

    /**
    *功能描述：用户查询所有挂号记录
    *@param userName 该用户的账号
    *@return
    *           List 挂号信息
    */
    @Override
    public List<RegisterInformation> selectRegisterInformationByUserName(String userName) {

        List<RegisterInformation> registerInformationList = new ArrayList<>();

        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        String selectSql = "select * from register_information where user_name = ?";
        String orderNumber;//订单编号
        String registeredStatus;//挂号状态（预约、成功就诊、取消）
        String registeredLevel;//挂号级别
        String registeredPayment;//挂号费用
        String registeredDepartment;//挂号科室
        String consultDoctorName;//看诊医生姓名
        String consultDoctorId;//看诊医生的工号
        String patientVisitorName;//就诊人姓名
        String patientVisitorSex;//就诊人性别
        String patientVisitorId;//就诊人身份证
        Date visitDate;//就诊日期
        String visitLocation;//就诊地点
        String visitTime;//就诊时间
        int patientVisitorAge;//就诊人年龄

        try {
            preparedStatement = connection.prepareStatement(selectSql);
            preparedStatement.setString(1,userName);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()){
                orderNumber = resultSet.getString(2);//订单编号
                registeredStatus = resultSet.getString(3);//挂号状态（预约、成功、取消）
                registeredLevel = resultSet.getString(4);//挂号级别
                registeredPayment = resultSet.getString(5);//挂号费用
                registeredDepartment = resultSet.getString(6);//挂号科室
                consultDoctorName = resultSet.getString(7);//看诊医生姓名
                consultDoctorId = resultSet.getString(8);
                patientVisitorName = resultSet.getString(9);//就诊人姓名
                patientVisitorSex = resultSet.getString(10);//就诊人性别
                patientVisitorId = resultSet.getString(11);//就诊人身份证
                visitDate = resultSet.getDate(12);//就诊时间
                visitLocation = resultSet.getString(13);//就诊地点
                visitTime = resultSet.getString(15);
                patientVisitorAge = Integer.parseInt(resultSet.getString(16));
                registerInformationList.add(new RegisterInformation(orderNumber,registeredStatus,
                        registeredLevel,registeredPayment,registeredDepartment,consultDoctorName,consultDoctorId,
                        patientVisitorName,patientVisitorSex,patientVisitorId,visitDate,visitLocation,userName,visitTime,patientVisitorAge));
            }

        }catch (Exception e){
            System.out.println(e.getMessage());
        }finally {
            JDBCUtils.close(connection,preparedStatement,resultSet);
        }

        return registerInformationList;
    }

    /**
     *功能描述：取消预约
     *@param orderNumber 预约挂号的订单编号
     *@return
     *           false 取消预约失败
     *           true 取消预约成功
     */
    @Override
    public boolean cancelAppointment(String orderNumber) {
        boolean cancelStatus = false;

        String updateSql = "update register_information set registered_status = ? where order_number=?";
        String registeredStatus="已取消";

        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement = null;

        try {
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(updateSql);
            preparedStatement.setString(1,registeredStatus);
            preparedStatement.setString(2,orderNumber);

            int count = preparedStatement.executeUpdate();

            if(count==1){
                connection.commit();
                cancelStatus = true;
                System.out.println("取消预约成功");
            }else {
                connection.rollback();
                System.out.println("取消预约失败");
            }

        }catch (Exception e){
            System.out.println(e.getMessage());
        }finally {

        }
        return cancelStatus;
    }

    /**
    *功能描述：插入一条挂号信息
    *@param registerInformation 挂号信息
    *@return
    *           false 插入失败
    *           true 插入成功
    */
    @Override
    public boolean insertRegisterInformation(RegisterInformation registerInformation) {
        boolean insertStatus = false;

        String insertSql = "insert into register_information(order_number,registered_status,registered_level," +
                "registered_payment,registered_department,consult_doctor_name,consult_doctor_id,patient_visitor_name," +
                "patient_visitor_sex,patient_visitor_idcard,visit_location,visit_date,user_name,visit_time)" +
                "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        String orderNumber = registerInformation.getOrderNumber();//订单编号
        String registeredStatus = registerInformation.getRegisteredStatus();//挂号状态（预约、成功、取消）
        String registeredLevel = registerInformation.getRegisteredLevel();//挂号级别
        String registeredPayment = registerInformation.getRegisteredPayment();//挂号费用
        String registeredDepartment = registerInformation.getRegisteredDepartment();//挂号科室
        String consultDoctorName = registerInformation.getConsultDoctorName();//看诊医生姓名
        String consultDoctorId = registerInformation.getConsultDoctorId();
        String patientVisitorName = registerInformation.getPatientVisitorName();//就诊人姓名
        String patientVisitorSex = registerInformation.getPatientVisitorSex();//就诊人性别
        String patientVisitorId = registerInformation.getPatientVisitorId();//就诊人身份证
        Date visitDate = registerInformation.getVisitDate();//就诊日期
        String visitLocation = registerInformation.getVisitLocation();//就诊地点
        String userName = registerInformation.getUserName();//用户账号
        String visitTime = registerInformation.getVisitTime();

        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement = null;

        try {
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(insertSql);
            preparedStatement.setString(1,orderNumber);
            preparedStatement.setString(2,registeredStatus);
            preparedStatement.setString(3,registeredLevel);
            preparedStatement.setString(4,registeredPayment);
            preparedStatement.setString(5, registeredDepartment);
            preparedStatement.setString(6,consultDoctorName);
            preparedStatement.setString(7,consultDoctorId);
            preparedStatement.setString(8,patientVisitorName);
            preparedStatement.setString(9,patientVisitorSex);
            preparedStatement.setString(10,patientVisitorId);
            preparedStatement.setString(11,visitLocation);
            preparedStatement.setDate(12,visitDate);
            preparedStatement.setString(13,userName);
            preparedStatement.setString(14,visitTime);

            int count = preparedStatement.executeUpdate();
            if(count==1){
                connection.commit();
                insertStatus = true;
                System.out.println("插入挂号信息成功");
            }else {
                connection.rollback();
                System.out.println("插入挂号信息失败");
            }

        }catch (Exception e){
            System.out.println(e.getMessage());
            e.printStackTrace();
        }finally {
            JDBCUtils.close(connection,preparedStatement,null);
        }

        return insertStatus;
    }

    /**
    *功能描述：查看用户下的所有就诊人信息
    *@param
    *@return
    *           List<Patient> 就诊人信息数组
    */
    @Override
    public List<Patient> selectAllPatients() {

        List<Patient> patientList = new ArrayList<>();

        String selectSql = "select * from patient_user";
        String userName;//用户账号
        String name;//就诊人姓名
        String sex;//就诊人性别
        int age;//就诊人年龄
        String phone;//就诊人电话
        String id;//就诊人身份证

        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            preparedStatement = connection.prepareStatement(selectSql);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()){
                userName = resultSet.getString(2);
                name = resultSet.getString(3);
                sex = resultSet.getString(4);
                age = resultSet.getInt(5);
                phone = resultSet.getString(6);
                id = resultSet.getString(7);
                patientList.add(new Patient(userName,name,sex,age,phone,id));
            }

        }catch (Exception e){
            System.out.println(e.getMessage());
        }finally {
            JDBCUtils.close(connection,preparedStatement,resultSet);
        }



        return patientList;
    }

    /**
    *功能描述：根据就诊人的身份证号查找就诊人信息
    *@param id 就诊人的身份证号
    *@return
    *           Patient 就诊人信息
    */
    @Override
    public Patient selectPatientById(String id) {

        Patient patient = null;

        String selectSql = "select * from patient_user where idcard =?";
        String userName;//用户账号
        String name;//就诊人姓名
        String sex;//就诊人性别
        int age;//就诊人年龄
        String phone;//就诊人电话

        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            preparedStatement = connection.prepareStatement(selectSql);
            preparedStatement.setString(1,id);
            resultSet = preparedStatement.executeQuery();

            resultSet.next();//不加就会报异常 java.sql.SQLException: Before start of result set
            userName = resultSet.getString(2);
            name = resultSet.getString(3);
            sex = resultSet.getString(4);
            age = resultSet.getInt(5);
            phone = resultSet.getString(6);

            patient = new Patient(userName,name,sex,age,phone,id);

        }catch (Exception e){
            System.out.println(e.getMessage());
        }finally {
            JDBCUtils.close(connection,preparedStatement,resultSet);
        }


        return patient;
    }

    /**
    *功能描述：添加就诊人
    *@param patient 就诊人信息
    *@return
    *           false 添加失败
    *           true 添加成功
    */
    @Override
    public boolean addPatient(Patient patient) {
        boolean addStatus = false;

        String insertSql = "insert into patient_user(username,name,sex,age,phone,idcard) values(?,?,?,?,?,?,?)";
        String userName = patient.getUserName();//用户账号
        String name = patient.getName();//就诊人姓名
        String sex = patient.getSex();//就诊人性别
        int age = patient.getAge();//就诊人年龄
        String phone = patient.getPhone();//就诊人电话
        String id = patient.getId();//就诊人身份证

        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement = null;

        try {
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(insertSql);
            preparedStatement.setString(1,userName);
            preparedStatement.setString(2,name);
            preparedStatement.setString(3,sex);
            preparedStatement.setInt(4, age);
            preparedStatement.setString(5,phone);
            preparedStatement.setString(6,id);

            int count = preparedStatement.executeUpdate();
            if(count==1){
                connection.commit();
                addStatus = true;
                System.out.println("添加就诊人成功");
            }else {
                connection.rollback();
                System.out.println("添加就诊人失败");
            }

        }catch (Exception e){
            System.out.println(e.getMessage());
        }finally {
            JDBCUtils.close(connection,preparedStatement,null);
        }

        return addStatus;
    }

    /**
    *功能描述：通过就诊人身份证删除就诊人信息
    *@param id 就诊人身份证号
    *@return
    *           false 删除失败
    *           true 删除成功
    */
    @Override
    public boolean deletePatientById(String id) {
        boolean deleteStatus = false;

        String deleteSql = "delete * from patient_user where idcard=?";

        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement = null;

        try {
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(deleteSql);
            preparedStatement.setString(1,id);
            int count = preparedStatement.executeUpdate();

            if(count==1){
                connection.commit();
                deleteStatus = true;
                System.out.println("删除成功");
            }else {
                connection.rollback();
                System.out.println("删除失败");
            }

        }catch (Exception e){
            System.out.println(e.getMessage());
        }finally {
            JDBCUtils.close(connection,preparedStatement,null);
        }

        return deleteStatus;
    }

    /**
    *功能描述：修改就诊人的电话
    *@param id  就诊人身份证
    *@param phone 就诊人电话
    *@return
    *           false 修改就诊人电话失败
    *           true 修改就诊人电话成功
    */
    @Override
    public boolean updatePatientPhoneById(String id, String phone) {
        boolean updateStatus = false;

        String updateSql = "update patient_user set phone = ? where idcard = ?";

        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement = null;

        try {
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(updateSql);
            preparedStatement.setString(1,phone);
            preparedStatement.setString(2,id);

            int count = preparedStatement.executeUpdate();

            if(count==1){
                connection.commit();
                updateStatus = true;
                System.out.println("修改就诊人电话成功");
            }else {
                connection.rollback();
                System.out.println("修改就诊人电话失败");
            }

        }catch (Exception e){
            System.out.println(e.getMessage());
        }finally {

        }

        return updateStatus;
    }

    /**
    *功能描述：用户查看排班信息
    *@param department 科室
    *@return
    *           Map<Date,List<ScheduleInformation>> 科室排班信息
    */
    @Override
    public Map<Date, List<ScheduleInformation>> selectScheduleInformation(String department) {

        Map<Date,List<ScheduleInformation>> scheduleInformationMap = new HashMap<>();
        List<ScheduleInformation> scheduleInformationList = null;
        ScheduleInformation scheduleInformation = null;

        String selectSql = "select * from schedule_information where department=?";
        Date date = null;

        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            preparedStatement = connection.prepareStatement(selectSql);
            preparedStatement.setString(1,department);

            resultSet = preparedStatement.executeQuery();

            while(resultSet.next()){
                date = resultSet.getDate(5);
                scheduleInformation = new ScheduleInformation(resultSet.getString(2)
                        ,resultSet.getString(3), resultSet.getString(4),resultSet.getDate(5)
                        , resultSet.getString(6),resultSet.getString(7),resultSet.getInt(8)
                        ,resultSet.getString(9));

                if(scheduleInformationMap.containsKey(date)){
                    scheduleInformationMap.get(date).add(scheduleInformation);
                }
                else {
                    scheduleInformationList = new ArrayList<>();
                    scheduleInformationList.add(scheduleInformation);
                    scheduleInformationMap.put(date,scheduleInformationList);
                }
            }

            //按照日期排序（升序）
            Map<Date, List<ScheduleInformation>> sortMap = new TreeMap<>(new Comparator<Date>() {
                @Override
                public int compare(Date o1, Date o2) {
                    return o1.compareTo(o2);
                }
            });
            sortMap.putAll(scheduleInformationMap);
            scheduleInformationMap = sortMap;

        }catch (Exception e){
            System.out.println(e.getMessage());
        }finally {
            JDBCUtils.close(connection,preparedStatement,resultSet);
        }

        return scheduleInformationMap;
    }

}
